const Koa = require('koa')
const Router = require('koa-router')
const Cors = require('koa-cors')
const BodyParser = require('koa-bodyparser')
const mysql = require('mysql')
const app = new Koa();
const router = new Router();

// 连接数据库
const con = mysql.createConnection({
    port: "3306",
    host: 'localhost',
    user:"root",
    password:"123456",
    database: "blog-12a"
})
con.connect()
function query(sql) {
    return new Promise((resolve,reject) => {
        con.query(sql, (err,data)=>{
            if(err) {
                reject(err)
            } else {
                resolve(data)
            }
        })
    })
}
// 获取博客列表
router.get('/blog/list', async (ctx) => {
    // 分页  模糊查询  排序（sortBy 根据哪个字段 sortType 正序还是倒叙）  条件查询
    const {pageIndex,pageSize,keyword,sortBy,sortType, id} = ctx.query;
    // where order limit
    let sql = `select * from blogs where 1=1 `
    let sqlCount = `select count(id) as total from blogs where 1=1 `
    if(id) {
        sql += `and id = ${id} `
    }
    if(keyword) {
        // 21 条  模糊查找出来10条
        // 但是总数还是 21
        // 假设前端 每页5条那么前端 几页 5页
        // 当前点击第5页
        sql += `and title like "%${keyword}%" `
        sqlCount += `and title like "%${keyword}%" `
    }
    if(sortBy && sortType) {
        sql += `order by ${sortBy} ${sortType} `
    }
    if(pageIndex &&pageSize) {
        sql += `limit ${(pageIndex-1)*pageSize},${pageSize}`
    }
    const list = await query(sql)
    const count = await query(sqlCount)
    ctx.body = {
        code:1,
        data: {
            list,
            total: count[0].total
        }
    }
})
// 添加博客
router.post('/blog/list', async (ctx) => {
    const {title,content,author} = ctx.request.body;
    const sql = `insert into blogs(title,content,author,create_time) values("${title}","${content}","${author}",${Date.now()})`
    const data = await query(sql)
    if(data.affectedRows === 1) {
        ctx.body = {
            code:1,
            data: {
                id:data.insertId
            },
            message:'添加成功'
        }
    } else {
        ctx.body = {
            code:-1,
            message:'添加失败'
        }
    }
    //     "affectedRows": 1,  受影响的行数
    //     "insertId": 22,  新增数据的id
})
// 编辑博客
router.put('/blog/list', async (ctx) => {
    const {id,title,content} = ctx.request.body;
    if(!title || !content) {
        ctx.body = {
            code:-1,
            message:"参数缺失"
        };
        return;
    }
    let sql = `update blogs set title="${title}",content="${content}" `
    sql += `where id="${id}"`
    const data = await query(sql)
    if(data.affectedRows === 1) {
        ctx.body = {
            code:1,
            message:'编辑成功'
        }
    } else {
        ctx.body = {
            code:-1,
            message:'编辑失败'
        }
    }
})
// 删除博客
router.delete('/blog/list/:id', async(ctx) => {
    const {id} = ctx.params;
    const sql = `delete from blogs where id=${id}`
    const data = await query(sql)
    if(data.affectedRows === 1) {
        ctx.body = {
            code:1,
            message:'删除成功'
        }
    } else {
        ctx.body = {
            code:-1,
            message:'删除失败'
        }
    }
})




app.use(Cors())
app.use(BodyParser())
app.use(router.routes())

app.listen(8888, () => {
    console.log('listen to8888')
})
